-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE NTDB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sushil Mittal
-- Create date: 
-- Description:	
-- =============================================
IF OBJECT_ID('sp_CountNonNullRowsInThisTable') IS NOT NULL
    DROP PROCEDURE sp_CountNonNullRowsInThisTable

GO
CREATE PROCEDURE sp_CountNonNullRowsInThisTable 
	-- Add the parameters for the stored procedure here
	@TableName NVARCHAR(100)
AS
BEGIN
	-- Insert statements for procedure here	SET NOCOUNT ON;
	DECLARE @RowsToProcess  INT
	DECLARE @CurrentRow     INT
	DECLARE @sql NVARCHAR(4000)
	DECLARE @ColumnName NVARCHAR(100)

	IF OBJECT_ID('tableOfTablesColumnsNTDB') IS NOT NULL
		DROP TABLE tableOfTablesColumnsNTDB	
	IF OBJECT_ID('tableOfTablesColumnsNTDB2') IS NOT NULL
		DROP TABLE tableOfTablesColumnsNTDB2
	IF OBJECT_ID('tableOfTablesColumnsNTDB3') IS NOT NULL
	    DROP TABLE tableOfTablesColumnsNTDB3    

	CREATE TABLE tableOfTablesColumnsNTDB (RowID int not null primary key identity(1,1),
											sName VARCHAR(50),
											tName VARCHAR(50),
											cName VARCHAR(50))
	
	CREATE TABLE tableOfTablesColumnsNTDB2 (TableName NVARCHAR(100),
											ColumnName NVARCHAR(100),
											nRowsAll INT
											)

	CREATE TABLE tableOfTablesColumnsNTDB3 (TableName NVARCHAR(100),
											ColumnName NVARCHAR(100),
											nRowsNonNull INT
											)
	

	SET NOCOUNT ON
	INSERT INTO tableOfTablesColumnsNTDB
	SELECT s.name SchemaName, t.name TableName, c.name ColumnName
	FROM sys.columns c INNER JOIN
		 sys.tables t ON c.object_id = t.object_id INNER JOIN
		 sys.schemas s ON t.schema_id = s.schema_id
     
	WHERE t.name = @TableName

	SET @RowsToProcess=@@ROWCOUNT	
		
	--SELECT * FROM tableOfTablesColumnsNTDB
	
	SET @CurrentRow=0
	WHILE @CurrentRow<@RowsToProcess
	BEGIN
		SET @CurrentRow=@CurrentRow+1
		PRINT 'Processing row '+CONVERT(VARCHAR,@CurrentRow)+' of '+CONVERT(VARCHAR,@RowsToProcess);
		
		SELECT @ColumnName = cName FROM tableOfTablesColumnsNTDB WHERE @CurrentRow = RowID
		
		IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'INC_KEY' AND Object_ID = Object_ID(@TableName))
			BEGIN
				SELECT @sql = N'SELECT @cnt_all = COUNT(DISTINCT INC_KEY) FROM dbo.'+@TableName
			END
		ELSE
			BEGIN
				SELECT @sql = N'SELECT @cnt_all = COUNT(*) FROM dbo.'+@TableName
			END
		
		INSERT tableOfTablesColumnsNTDB2(TableName,ColumnName,nRowsAll) 
							      EXEC('DECLARE @cnt_all INT
								  EXEC sp_executesql N''' + @sql + ''',
							      N''@TableName nvarchar(100), @ColumnName nvarchar(100),
								  @cnt_all INT OUTPUT'',
							      @TableName = ''' + @TableName + ''',
								  @ColumnName = ''' + @ColumnName + ''',
	                              @cnt_all = @cnt_all OUTPUT
								  SELECT ''' + @TableName+ ''',''' +@ColumnName+''',@cnt_all')
	
	
		IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'INC_KEY' AND Object_ID = Object_ID(@TableName))
			BEGIN
				SELECT @sql = N'SELECT @cnt_nonNull = COUNT(DISTINCT INC_KEY) FROM dbo.'+@TableName+' WHERE '+@ColumnName+' IS NOT NULL'
			END
		ELSE
			BEGIN
				SELECT @sql = N'SELECT @cnt_nonNull = COUNT(*) FROM dbo.'+@TableName+' WHERE '+@ColumnName+' IS NOT NULL'
			END
		
		INSERT tableOfTablesColumnsNTDB3(TableName,ColumnName,nRowsNonNull) 
							      EXEC('DECLARE @cnt_nonNull INT
								  EXEC sp_executesql N''' + @sql + ''',
							      N''@TableName nvarchar(100), @ColumnName nvarchar(100),
								  @cnt_nonNull INT OUTPUT'',
						          @TableName = ''' + @TableName + ''',
							      @ColumnName = ''' + @ColumnName + ''',
								  @cnt_nonNull = @cnt_nonNull OUTPUT
								  SELECT ''' + @TableName+ ''',''' +@ColumnName+''',@cnt_nonNull')
    
	END
--	SELECT * FROM tableOfTablesColumnsNTDB2

	SELECT Table1.TableName, Table1.ColumnName, Table1.nRowsAll, Table2.nRowsNonNull FROM tableOfTablesColumnsNTDB2 AS Table1, tableOfTablesColumnsNTDB3 AS Table2 
	WHERE Table1.TableName = Table2.TableName AND Table1.ColumnName = Table2.ColumnName

	DROP TABLE tableOfTablesColumnsNTDB	
	DROP TABLE tableOfTablesColumnsNTDB2
	DROP TABLE tableOfTablesColumnsNTDB3
END
GO
